package test;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DatabaseUtil {

	private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	private static final String URL = "jdbc:mysql://你自己的MYSQL地址:3306/hotel?characterEncoding=utf8&serverTimezone=GMT%2B8";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "root";

	private static final String SQL = "SELECT * FROM ";// 数据库操作

	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {

		}
	}

	/**
	 * 获取数据库连接
	 *
	 * @return
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {

		}
		return conn;
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {

			}
		}
	}

	/**
	 * 获取数据库下的所有表名
	 */
	public static List<String> getTableNames() {
		List<String> tableNames = new ArrayList<>();
		Connection conn = getConnection();
		ResultSet rs = null;
		try {
			// 获取数据库的元数据
			DatabaseMetaData db = conn.getMetaData();
			// 从元数据中获取到所有的表名
//			rs = db.getTables(null, null, null, new String[] { "TABLE" });
			rs = db.getTables(conn.getCatalog(), conn.getCatalog(), "%", new String[] { "TABLE" });
			while (rs.next()) {
				tableNames.add(rs.getString(3));
			}
		} catch (SQLException e) {
		} finally {
			try {
				rs.close();
				closeConnection(conn);
			} catch (SQLException e) {
			}
		}
		return tableNames;
	}

	/**
	 * 获取表中所有字段名称
	 * 
	 * @param tableName 表名
	 * @return
	 */
	public static List<String> getColumnNames(String tableName) {
		List<String> columnNames = new ArrayList<>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			// 表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnNames.add(rsmd.getColumnName(i + 1));
			}
		} catch (SQLException e) {
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
					closeConnection(conn);
				} catch (SQLException e) {
				}
			}
		}
		return columnNames;
	}

	/**
	 * 获取表中所有字段类型
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<String> getColumnTypes(String tableName) {
		List<String> columnTypes = new ArrayList<>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		try {
			pStemt = conn.prepareStatement(tableSql);
			// 结果集元数据
			ResultSetMetaData rsmd = pStemt.getMetaData();
			// 表列数
			int size = rsmd.getColumnCount();
			for (int i = 0; i < size; i++) {
				columnTypes.add(rsmd.getColumnTypeName(i + 1));
			}
		} catch (SQLException e) {
		} finally {
			if (pStemt != null) {
				try {
					pStemt.close();
					closeConnection(conn);
				} catch (SQLException e) {
				}
			}
		}
		return columnTypes;
	}

	/**
	 * 获取表中字段的所有注释
	 * 
	 * @param tableName
	 * @return
	 */
	public static List<String> getColumnComments(String tableName) {
		List<String> columnTypes = new ArrayList<>();
		// 与数据库的连接
		Connection conn = getConnection();
		PreparedStatement pStemt = null;
		String tableSql = SQL + tableName;
		List<String> columnComments = new ArrayList<>();// 列名注释集合
		ResultSet rs = null;
		try {
			pStemt = conn.prepareStatement(tableSql);
			rs = pStemt.executeQuery("show full columns from " + tableName);
			while (rs.next()) {
				columnComments.add(rs.getString("Comment"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
					closeConnection(conn);
				} catch (SQLException e) {
				}
			}
		}
		return columnComments;
	}

	public static void genAllTables() throws Exception {
		List<String> tableNames = getTableNames();
		for (String tableName : tableNames) {
			List<String> columnNames = getColumnNames(tableName);
			List<String> columnTypes = getColumnTypes(tableName);
			List<String> columnComments = getColumnComments(tableName);
			
			//实体类ClassName
			StringBuffer classNameBuffer = new StringBuffer();
			String[] tableSplit = tableName.split("_");
			for (String item : tableSplit) {
				classNameBuffer.append(item.substring(0, 1).toUpperCase()).append(item.substring(1, item.length()));
			}
			System.out.println();
			System.out.println("Java Class Name = " + classNameBuffer.toString());
			System.out.println();
			//实体类中的字段属性名称
			for (String columnItem : columnNames) {
				StringBuffer columnNameBuffer = new StringBuffer();
				String[] columnSplit = columnItem.split("_");
				int length = columnSplit.length;
				for (int i = 0; i < length; i++) {
					String item = columnSplit[i];
					if (i == 0) {
						columnNameBuffer.append(item.substring(0, 1).toLowerCase()).append(item.substring(1, item.length()));
					} else {
						columnNameBuffer.append(item.substring(0, 1).toUpperCase()).append(item.substring(1, item.length()));
					}
				}
				System.out.println("Java columnName Name = " + columnNameBuffer.toString());
			}
			System.out.println();
			
			//数据库中的字段类型
			for (String columnItem : columnTypes) {
				System.out.println("Java columnType Name = " +columnItem);
			}
			System.out.println();
			
			//数据库中的字段注释
			for (String columnComment : columnComments) {
				System.out.println("Java columnComment Name = " +columnComment);
			}
		}
	}

	public static void main(String[] args) throws Exception {
//		List<String> tableNames = getTableNames();
//		System.out.println("tableNames:" + tableNames);
//		for (String tableName : tableNames) {
//			System.out.println();
//			System.out.println("tableName:" + tableName);
//			System.out.println("ColumnNames:" + getColumnNames(tableName));
//			System.out.println("ColumnTypes:" + getColumnTypes(tableName));
//			System.out.println("ColumnComments:" + getColumnComments(tableName));
//		}
		genAllTables();
	}
}